// 台球桌管理路由
const express = require('express');
const router = express.Router();
const { query } = require('../config/database');
const { authenticateToken, requireAdmin, optionalAuth } = require('../middleware/auth');
const { validateTable, validateId, validatePagination } = require('../middleware/validation');
const { calculatePagination } = require('../utils/helpers');
const ResponseHelper = require('../utils/response');

// 获取台球桌列表（公开接口）
router.get('/', optionalAuth, validatePagination, async (req, res) => {
  try {
    const { page, pageSize, offset, limit } = calculatePagination(req.query.page, req.query.pageSize);
    const { type, status, available_only } = req.query;

    // 构建查询条件
    let whereClause = 'WHERE 1=1';
    let queryParams = [];

    if (type) {
      whereClause += ' AND type = ?';
      queryParams.push(type);
    }

    if (status) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    if (available_only === 'true') {
      whereClause += ' AND status = "available"';
    }

    // 获取台球桌总数
    const countResult = await query(
      `SELECT COUNT(*) as total FROM tables ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    // 获取台球桌列表
    const tables = await query(
      `SELECT id, name, type, hourly_rate, status, description, created_at, updated_at 
       FROM tables ${whereClause} 
       ORDER BY name 
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    ResponseHelper.paginated(res, tables, total, page, pageSize, '获取台球桌列表成功');

  } catch (error) {
    console.error('获取台球桌列表错误:', error);
    ResponseHelper.serverError(res, '获取台球桌列表失败');
  }
});

// 获取台球桌详情
router.get('/:id', optionalAuth, validateId, async (req, res) => {
  try {
    const tableId = req.params.id;

    const tables = await query(
      'SELECT id, name, type, hourly_rate, status, description, created_at, updated_at FROM tables WHERE id = ?',
      [tableId]
    );

    if (tables.length === 0) {
      return ResponseHelper.notFound(res, '台球桌不存在');
    }

    ResponseHelper.success(res, tables[0], '获取台球桌详情成功');

  } catch (error) {
    console.error('获取台球桌详情错误:', error);
    ResponseHelper.serverError(res, '获取台球桌详情失败');
  }
});

// 创建台球桌（管理员）
router.post('/', authenticateToken, requireAdmin, validateTable, async (req, res) => {
  try {
    const { name, type, location, hourly_rate, status = 'available', description } = req.body;

    // 检查台球桌名称是否已存在
    const existingTables = await query('SELECT id FROM tables WHERE name = ?', [name]);
    if (existingTables.length > 0) {
      return ResponseHelper.error(res, '台球桌名称已存在', 400);
    }

    // 创建台球桌
    const result = await query(
      `INSERT INTO tables (name, type, location, hourly_rate, status, description, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [name, type, location || '', hourly_rate, status, description || '']
    );

    // 获取创建的台球桌信息
    const newTable = await query(
      'SELECT id, name, type, hourly_rate, status, description, created_at, updated_at FROM tables WHERE id = ?',
      [result.insertId]
    );

    ResponseHelper.success(res, newTable[0], '台球桌创建成功', 201);

  } catch (error) {
    console.error('创建台球桌错误:', error);
    ResponseHelper.serverError(res, '创建台球桌失败');
  }
});

// 更新台球桌信息（管理员）
router.put('/:id', authenticateToken, requireAdmin, validateId, validateTable, async (req, res) => {
  try {
    const tableId = req.params.id;
    const { name, type, location, hourly_rate, status, description } = req.body;

    // 检查台球桌是否存在
    const existingTables = await query('SELECT id FROM tables WHERE id = ?', [tableId]);
    if (existingTables.length === 0) {
      return ResponseHelper.notFound(res, '台球桌不存在');
    }

    // 检查台球桌名称是否已被其他台球桌使用
    if (name) {
      const duplicateTables = await query(
        'SELECT id FROM tables WHERE name = ? AND id != ?',
        [name, tableId]
      );

      if (duplicateTables.length > 0) {
        return ResponseHelper.error(res, '台球桌名称已被使用', 400);
      }
    }

    // 处理 undefined 值，转换为 null
    const params = [
      name !== undefined ? name : null,
      type !== undefined ? type : null,
      location !== undefined ? location : null,
      hourly_rate !== undefined ? hourly_rate : null,
      status !== undefined ? status : null,
      description !== undefined ? description : null,
      tableId
    ];

    // 更新台球桌信息
    await query(
      `UPDATE tables SET
       name = COALESCE(?, name),
       type = COALESCE(?, type),
       location = COALESCE(?, location),
       hourly_rate = COALESCE(?, hourly_rate),
       status = COALESCE(?, status),
       description = COALESCE(?, description),
       updated_at = NOW()
       WHERE id = ?`,
      params
    );

    // 获取更新后的台球桌信息
    const updatedTable = await query(
      'SELECT id, name, type, location, hourly_rate, status, description, created_at, updated_at FROM tables WHERE id = ?',
      [tableId]
    );

    ResponseHelper.success(res, updatedTable[0], '台球桌信息更新成功');

  } catch (error) {
    console.error('更新台球桌信息错误:', error);
    ResponseHelper.serverError(res, '更新台球桌信息失败');
  }
});

// 更新台球桌状态
router.put('/:id/status', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const tableId = req.params.id;
    const { status } = req.body;

    if (!['available', 'occupied', 'maintenance'].includes(status)) {
      return ResponseHelper.error(res, '台球桌状态无效', 400);
    }

    // 检查台球桌是否存在
    const tables = await query('SELECT id, status FROM tables WHERE id = ?', [tableId]);
    if (tables.length === 0) {
      return ResponseHelper.notFound(res, '台球桌不存在');
    }

    // 更新台球桌状态
    await query(
      'UPDATE tables SET status = ?, updated_at = NOW() WHERE id = ?',
      [status, tableId]
    );

    ResponseHelper.success(res, {
      table_id: tableId,
      old_status: tables[0].status,
      new_status: status
    }, '台球桌状态更新成功');

  } catch (error) {
    console.error('更新台球桌状态错误:', error);
    ResponseHelper.serverError(res, '更新台球桌状态失败');
  }
});

// 删除台球桌（管理员）
router.delete('/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const tableId = req.params.id;

    // 检查台球桌是否存在
    const tables = await query('SELECT id FROM tables WHERE id = ?', [tableId]);
    if (tables.length === 0) {
      return ResponseHelper.notFound(res, '台球桌不存在');
    }

    // 检查台球桌是否有未完成的订单
    const activeOrders = await query(
      'SELECT id FROM orders WHERE table_id = ? AND status IN ("pending", "confirmed")',
      [tableId]
    );

    if (activeOrders.length > 0) {
      return ResponseHelper.error(res, '台球桌有未完成的订单，无法删除', 400);
    }

    // 删除台球桌
    await query('DELETE FROM tables WHERE id = ?', [tableId]);

    ResponseHelper.success(res, null, '台球桌删除成功');

  } catch (error) {
    console.error('删除台球桌错误:', error);
    ResponseHelper.serverError(res, '删除台球桌失败');
  }
});

module.exports = router;
